<?php

/**
 * @file
 * A collection of fucntions.
 */

/**
 * Sys the webform translation table with the drupal language table
 */
function _sys_webform_transaltion() {
  $drupal_languages = language_list(); //print_r($drupal_languages);exit;

  foreach ($drupal_languages as $value) {
    $sql = "select * from {webform_translation} where lcode='" . $value->language . "'";
    $result = db_query($sql);
    $row = $result->fetchAssoc(); //print_r($row);exit;

    if (isset($row['lid']) && $row['lid'] > 0) {
      $sql = "update {webform_translation} set name='" . $value->name . "',enable=" . $value->enabled . " where lid=" . $row['lid'];
      db_query($sql);
    } else {
      $sql = "insert into {webform_translation} (lcode,name,enable)values('" . $value->language . "','" . $value->name . "'," . $value->enabled . ")";
      db_query($sql);
    }
  }
}

function _get_webform_translation($filter = '') {
  static $lang = array();

  if (!isset($lang['name']) || count($lang) <= 0) {
    _sys_webform_transaltion();

    $sql = "SELECT * FROM {webform_translation} WHERE 1=1 AND enable=1 ORDER BY name";
    $result = db_query($sql);
    foreach ($result as $o) {
      if (!empty($filter) && $o->lcode == $filter) {
        continue;
      }

      $lang['name'][$o->lcode] = $o->name;
      $lang['lid'][$o->lcode] = $o->lid;
    }
  }//print_r($lang);exit;

  return $lang;
}

function _get_webform_component_translation($nid, $lid, $cid = 0) {
  $translation = array();

  if ($nid <= 0 || $lid <= 0) {
    return $translation;
  }

  if ($cid > 0) {
    $result = db_query('SELECT c.* FROM {webform_component_translation} c WHERE c.nid=:nid AND c.cid=:cid AND c.lid=:lid', array(':nid' => $nid, ':cid' => $cid, ':lid' => $lid));
    //$translation = $result->fetch(); //object
    $translation = $result->fetchAssoc(); //arary
  } else {
    $result = db_query('SELECT c.* FROM {webform_component_translation} c WHERE c.nid=:nid AND c.lid=:lid', array(':nid' => $nid, ':lid' => $lid));
    foreach ($result as $o) {
      $translation[$o->cid] = $o;
    }
  }
  //print_r($translation);exit;

  return $translation;
}

// Return location lists array or site_code array
function location_get_list($country = WEBFORM_DEFAULT_COUNTRY, $site_code = '') {
  $data = array();

  $sql = "SELECT * FROM {webform_location} WHERE country_code=:country ORDER BY site_name";
  $result = db_query($sql, array(':country' => $country));
  foreach ($result as $o) {
    $data[$o->site_code] = $o;
  }

  if (!empty($site_code) && isset($data[$site_code])) {
    $return[$site_code] = $data[$site_code];
    return $return;
  }

  return $data;
}

// Return location sites list array or site_code array
function location_site_get_list($country = WEBFORM_DEFAULT_COUNTRY, $site_code = '') {
  $data = array();

  $country = strtoupper($country);
  $sql = "SELECT * FROM {webform_location_site} WHERE country_code=:country ORDER BY site_name";
  $result = db_query($sql, array(':country' => $country));
  foreach ($result as $o) {
    $data[$o->site_code] = $o;
  }

  if (!empty($site_code) && isset($data[$site_code])) {
    $return[$site_code] = $data[$site_code];
    return $return;
  }

  return $data;
}

// Return location citys list array or site_code array
function location_city_get_list($country = WEBFORM_DEFAULT_COUNTRY) {
  $data = array();

  $country = strtoupper($country);
  $sql = "SELECT * FROM {webform_location_city} WHERE country_code=:country ORDER BY city_name";
  $result = db_query($sql, array(':country' => $country));
  foreach ($result as $o) {
    $data[$o->city_name] = $o;
  }

  return $data;
}

// Return company lists array or company_code array
function company_get_list($country = WEBFORM_DEFAULT_COUNTRY, $company_code = '') {
  $company = array();

  if (!empty($company_code)) {
    $sql = "SELECT * FROM {webform_company} WHERE country=:country AND company_code=:code";
    $result = db_query($sql, array(':country' => $country, ':code' => $company_code));
    $company = $result->fetchAssoc();
  } else {
    $sql = "SELECT * FROM {webform_company} WHERE country_code='$country' ORDER BY long_name";
    $result = db_query($sql);
    foreach ($result as $o) {
      $company[$o->company_code] = $o;
    }
  }

  return $company;
}

// Return country lists array
function webform_get_country_list() {
  $data = array();
  
  $data[] = array('key' => 'AR', 'group' => true, 'value' => t('Argentina'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'AU', 'group' => true, 'value' => t('Australia & New Zealand'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'AT', 'group' => true, 'value' => t('Austria'), 'nid' => PG_INITIATION_FORM_NID);
  $data[] = array('key' => 'CL', 'group' => true, 'value' => t('Chile'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'CN', 'group' => true, 'value' => t('China'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'CO', 'group' => true, 'value' => t('Colombia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'HR', 'group' => true, 'value' => t('Croatia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'CZ', 'group' => true, 'value' => t('Czech Republic'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'FR', 'group' => true, 'value' => t('France'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'DE', 'group' => true, 'value' => t('Germany'), 'nid' => PG_INITIATION_FORM_NID);
  $data[] = array('key' => 'GR', 'group' => true, 'value' => t('Greece'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'HU', 'group' => true, 'value' => t('Hungary'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'IN', 'group' => true, 'value' => t('India'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'ID', 'group' => true, 'value' => t('Indonesia'), 'nid' => PG_IF_AU);
//  $data[] = array('key' => 'IT', 'group' => true, 'value' => t('Italy'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'JA', 'group' => false, 'value' => t('Japan'), 'nid' => PG_IF_JAP);
  $data[] = array('key' => 'MY', 'group' => true, 'value' => t('Malaysia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'MX', 'group' => false, 'value' => t('Mexico'), 'nid' => PG_IF_MEX);
  $data[] = array('key' => 'MA', 'group' => true, 'value' => t('Morocco'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'NL', 'group' => true, 'value' => t('Netherlands'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'PK', 'group' => true, 'value' => t('Pakistan'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'PH', 'group' => true, 'value' => t('Philippines'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'PL', 'group' => true, 'value' => t('Poland'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'PT', 'group' => true, 'value' => t('Portugal'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'RO', 'group' => true, 'value' => t('Romania, Bulgaria & Serbia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'RU', 'group' => true, 'value' => t('Russia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'SK', 'group' => true, 'value' => t('Slovakia'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'ES', 'group' => true, 'value' => t('Spain'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'CH', 'group' => true, 'value' => t('Switzerland'), 'nid' => PG_IF_AU);
  $data[] = array('key' => 'TR', 'group' => false, 'value' => t('Turkey'), 'nid' => PG_IF_TUR);
  $data[] = array('key' => 'VE', 'group' => true, 'value' => t('Venezuela'), 'nid' => PG_IF_AU);
  //ABCDEFGHIJKLMNOPQRSTUVWXYZ
  return $data;
}

// Return the country array from nid
function webform_get_country($nid) {
  $data = webform_get_country_list();
  $result = array();
  foreach ($data as $key => $val) {
    if ($nid == $val['nid']) {
      $result = $val;
      break;
    }
  }
  return $result;
}

function webform_get_nid_by_key($key) {
  $key = strtoupper($key);
  $data = webform_get_country_list();
  $result = array();
  foreach ($data as $k => $v) {
    if ($key == $v['key']) {
      $result = $v;
      break;
    }
  }
  return $result;
}

// Return the initiation form title array from nid
function webform_get_initiation_title($nid) {
  $result = array();
  $result['if_initiation'] = true;
  if (isset($nid) && $nid == PG_INITIATION_FORM_NID) {
    $result['initiation_title'] = t(PG_IF_TITLE_DE);
  } elseif (isset($nid) && $nid == PG_IF_JAP) {
    $result['initiation_title'] = t(PG_IF_TITLE_JAP);
  } elseif (isset($nid) && $nid == PG_IF_TUR) {
    $result['initiation_title'] = t(PG_IF_TITLE_TUR);
  } elseif (isset($nid) && $nid == PG_IF_MEX) {
    $result['initiation_title'] = t(PG_IF_TITLE_MEX);
  } elseif (isset($nid) && $nid == PG_IF_AU) {
    $result['initiation_title'] = t(PG_IF_TITLE_AU);
  } else {
    $result['if_initiation'] = false;
  }
  return $result;
}

// Return contact from country_code and type
function webform_get_contact($country_key, $type = 0) {
  $data = '';
  $sql = "SELECT * FROM {webform_contact} WHERE country_key = :country_key AND type=:type";
  $result = db_query($sql, array(':country_key' => $country_key, ':type' => $type));
  $contact = $result->fetch();
  if (isset($contact->email)) {
    $data = $contact->email;
  }
  //print_r($data);die;
  return $data;
}

/**
 * 
 * @param type $nid
 * @param type $country
 * @param type $pager_count
 * @return type $return(array)
 */
function get_sid_list_by_country($filters, $country, $pager_count = 0, $excep_cancle_sid = true, $header = NULL) {
  $query = db_select('webform_submitted_data', 'wsd', array('fetch' => PDO::FETCH_ASSOC));
  $query->fields('wsd', array('sid'));
  $query->leftJoin('webform_submissions', 'ws', 'wsd.sid = ws.sid');
  $query->fields('ws', array('submitted'));
  $query->condition('wsd.cid', COUNTRY_OF_DOMESTIC_CID);
  $query->condition('wsd.data', $country);
  foreach ($filters as $column => $value) {
    $query->condition('wsd.' . $column, $value);
  }
//  $cancel_sid_list_array = get_cancel_sid($filters['nid'], false);
//  if ($excep_cancle_sid && !empty($excep_cancle_sid)) {
//    $query->condition('wsd.sid', $cancel_sid_list_array, 'NOT IN');
//  } else {
//    $query->condition('wsd.sid', $cancel_sid_list_array, 'IN');
//  }
  if ($pager_count) {
    // Extending the query instatiates a new query object.
    $query = $query->extend('PagerDefault');
    $query->limit($pager_count);
  }
  if (is_array($header)) {
    // Extending the query instatiates a new query object.
    $query = $query->extend('TableSort');
    $query->orderByHeader($header);
  } else {
    $query->orderBy('wsd.sid', 'ASC');
  }
  $result = $query->execute();
  $return = array();
  foreach ($result as $row) {
    $return[] = $row['sid'];
  }
  return $return;
}

/**
 * get cancel sid data from webform_cancelled_submissions by nid;
 * @param type $nid
 * @return type
 */
function get_cancel_sid($nid = '', $pager_count = false) {
  if (!empty($nid)) {

    $query = db_select('webform_cancelled_submissions', 'wcs', array('fetch' => PDO::FETCH_ASSOC))
            ->fields('wcs', array('sid'))
            ->condition('wcs.nid', $nid);
    if ($pager_count) {
      // Extending the query instatiates a new query object.
      $query = $query->extend('PagerDefault');
      $query->limit($pager_count);
    }
    $result = $query->execute();
    $return = array(0);
    foreach ($result as $row) {
      $return[] = $row['sid'];
    }
  }  
  return $return;
}

function submission_cancel_access($sid) {
  global $user;
  $is_cancelled = false;
  $select = db_select('webform_cancelled_submissions', 'c')
          ->fields('c', array('sid'))
          ->condition('sid', $sid);
  $result = $select->execute()->fetchCol();
  if ($result) {
    $is_cancelled = true;
  }

  /////////////////////////
  $if_admin = false;
  if ($user->uid == 1) {
    $if_admin = true;
  }
  ////////////////////////

  if ((isset($_SESSION['is_crown_user']) && $_SESSION['is_crown_user'] == TRUE || $if_admin ) && !$is_cancelled) {
    return true;
  } else {
    return false;
  }
}

function submission_cancel_view_access($sid) {
  global $user;
  $is_cancelled = false;
  $select = db_select('webform_cancelled_submissions', 'c')
          ->fields('c', array('sid'))
          ->condition('sid', $sid);
  $result = $select->execute()->fetchCol();
  if ($result) {
    $is_cancelled = true;
  }

  if (!$is_cancelled) {
    return true;
  } else {
    return false;
  }
}

function submission_get_country($sid, $nid = NULL) {

  $query = db_select('webform_submitted_data', 'wsd', array('fetch' => PDO::FETCH_ASSOC))
          ->fields('wsd', array('data'))
          ->condition('wsd.cid', COUNTRY_OF_DOMESTIC_CID)
          ->condition('wsd.sid', $sid);
  $result = $query->execute()->fetchColumn();
  if ($result) {
    $country_data = webform_get_nid_by_key($result);
  }
  return $country_data;
}